-- @owner: opentestcase013
-- @date: 2023/7/13
-- @testpoint: ustore各种组合类型的二级分区表，修改非压缩表为压缩表

--step1:建ustore list_list分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_01 cascade;
create table t_alter_rowcompress_0097_01
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by list (month_code) subpartition by list (dept_code)
(
  partition p_201901 values ( '201902' )
  (
    subpartition p_201901_a values ( '1' ),
    subpartition p_201901_b values ( '2' )
  ),
  partition p_201902 values ( '201903' )
  (
    subpartition p_201902_a values ( '1' ),
    subpartition p_201902_b values ( '2' )
  )
);
insert into t_alter_rowcompress_0097_01 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_01 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_01 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_01 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_01 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_01 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_01 set (compresstype = 1);
checkpoint;
select relname, reloptions from pg_partition;
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_01'));
drop table if exists t_alter_rowcompress_0097_01 cascade;

--step2:建ustore list_hash分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_02 cascade;
create table t_alter_rowcompress_0097_02
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by list (month_code) subpartition by hash (dept_code)
(
  partition p_201901 values ( '201902' )
  (
    subpartition p_201901_a,
    subpartition p_201901_b
  ),
  partition p_201902 values ( '201903' )
  (
    subpartition p_201902_a,
    subpartition p_201902_b
  )
);
insert into t_alter_rowcompress_0097_02 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_02 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_02 values('201902', '3', '1', 1);
insert into t_alter_rowcompress_0097_02 values('201903', '4', '1', 1);
insert into t_alter_rowcompress_0097_02 values('201903', '5', '1', 1);
insert into t_alter_rowcompress_0097_02 values('201903', '6', '1', 1);

alter table t_alter_rowcompress_0097_02 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_02'));
drop table if exists t_alter_rowcompress_0097_02 cascade;

--step3:建ustore list_range分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_03 cascade;
create table t_alter_rowcompress_0097_03
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by list (month_code) subpartition by range (dept_code)
(
  partition p_201901 values ( '201902' )
  (
    subpartition p_201901_a values less than ('4'),
    subpartition p_201901_b values less than ('6')
  ),
  partition p_201902 values ( '201903' )
  (
    subpartition p_201902_a values less than ('3'),
    subpartition p_201902_b values less than ('6')
  )
);
insert into t_alter_rowcompress_0097_03 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_03 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_03 values('201902', '3', '1', 1);
insert into t_alter_rowcompress_0097_03 values('201903', '4', '1', 1);
insert into t_alter_rowcompress_0097_03 values('201903', '5', '1', 1);

alter table t_alter_rowcompress_0097_03 set (compresstype = 1);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_03'));
drop table if exists t_alter_rowcompress_0097_03 cascade;

--step4:建ustore range_list分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_04 cascade;
create table t_alter_rowcompress_0097_04
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by range (month_code) subpartition by list (dept_code)
(
  partition p_201901 values less than( '201903' )
  (
    subpartition p_201901_a values ('1'),
    subpartition p_201901_b values ('2')
  ),
  partition p_201902 values less than( '201904' )
  (
    subpartition p_201902_a values ('1'),
    subpartition p_201902_b values ('2')
  )
);
insert into t_alter_rowcompress_0097_04 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_04 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_04 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_04 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_04 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_04 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_04 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_04'));
drop table if exists t_alter_rowcompress_0097_04 cascade;

--step5:建ustore range_hash分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_05 cascade;
create table t_alter_rowcompress_0097_05
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by range (month_code) subpartition by hash (dept_code)
(
  partition p_201901 values less than( '201903' )
  (
    subpartition p_201901_a,
    subpartition p_201901_b
  ),
  partition p_201902 values less than( '201904' )
  (
    subpartition p_201902_a,
    subpartition p_201902_b
  )
);
insert into t_alter_rowcompress_0097_05 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_05 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_05 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_05 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_05 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_05 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_05 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_05'));
drop table if exists t_alter_rowcompress_0097_05 cascade;

--step6:建ustore range_range分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_06 cascade;
create table t_alter_rowcompress_0097_06
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by range (month_code) subpartition by range (dept_code)
(
  partition p_201901 values less than( '201903' )
  (
    subpartition p_201901_a values less than( '2' ),
    subpartition p_201901_b values less than( '3' )
  ),
  partition p_201902 values less than( '201904' )
  (
    subpartition p_201902_a values less than( '2' ),
    subpartition p_201902_b values less than( '3' )
  )
);
insert into t_alter_rowcompress_0097_06 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_06 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0097_06 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0097_06 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_06 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_06 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_06 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_06'));
drop table if exists t_alter_rowcompress_0097_06 cascade;

--step7:建ustore hash_list分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_07 cascade;
create table t_alter_rowcompress_0097_07
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by hash (month_code) subpartition by list (dept_code)
(
  partition p_201901
  (
    subpartition p_201901_a values ( '1' ),
    subpartition p_201901_b values ( '2' )
  ),
  partition p_201902
  (
    subpartition p_201902_a values ( '1' ),
    subpartition p_201902_b values ( '2' )
  )
);
insert into t_alter_rowcompress_0097_07 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_07 values('201901', '2', '1', 1);
insert into t_alter_rowcompress_0097_07 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_07 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_07 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_07 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_07 set (compresstype = 1);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_07'));
drop table if exists t_alter_rowcompress_0097_07 cascade;

--step8:建ustore hash_hash分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_08 cascade;
create table t_alter_rowcompress_0097_08
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by hash (month_code) subpartition by hash (dept_code)
(
  partition p_201901
  (
    subpartition p_201901_a,
    subpartition p_201901_b
  ),
  partition p_201902
  (
    subpartition p_201902_a,
    subpartition p_201902_b
  )
);
insert into t_alter_rowcompress_0097_08 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_08 values('201901', '2', '1', 1);
insert into t_alter_rowcompress_0097_08 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_08 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_08 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_08 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_08 set (compresstype = 1);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_08'));
drop table if exists t_alter_rowcompress_0097_08 cascade;

--step9:建ustore hash_range分区表并插入数据，修改为压缩表;expect:成功
drop table if exists t_alter_rowcompress_0097_09 cascade;
create table t_alter_rowcompress_0097_09
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by hash (month_code) subpartition by range (dept_code)
(
  partition p_201901
  (
    subpartition p_201901_a values less than ( '2' ),
    subpartition p_201901_b values less than ( '3' )
  ),
  partition p_201902
  (
    subpartition p_201902_a values less than ( '2' ),
    subpartition p_201902_b values less than ( '3' )
  )
);
insert into t_alter_rowcompress_0097_09 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_09 values('201901', '2', '1', 1);
insert into t_alter_rowcompress_0097_09 values('201901', '1', '1', 1);
insert into t_alter_rowcompress_0097_09 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0097_09 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0097_09 values('201903', '2', '1', 1);

alter table t_alter_rowcompress_0097_09 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0097_09'));
drop table if exists t_alter_rowcompress_0097_09 cascade;

--step10:清理环境;expect:成功
drop function if exists compress_func_findpath(character varying);